package org.liaoxuefeng.study;

import lombok.val;
import org.liaoxuefeng.entity.Student;
import org.testng.annotations.Test;

import java.sql.Array;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author: 默苍璃
 * @date: 2022-09-28 16:48
 * JDBC批量操作
 */
public class JDBCBatch {

    /**
     * JDBC连接的URL, 不同数据库有不同的格式:
     */
    final String JDBC_URL = "jdbc:mysql://localhost/jdbc?useSSL=false&serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8";
    final String JDBC_USER = "root";
    final String JDBC_PASSWORD = "root";

    List<Student> list = new ArrayList<>();

    //模拟插入数据
    {
        Student student = Student.builder().gender(1).score(100).name("和经济").grade(1).build();
        Student student1 = Student.builder().gender(2).score(99).name("分解机").grade(2).build();
        list.add(student);
        list.add(student1);
        for (int i = 0; i < 100; i++) {
            list.add(student);
            list.add(student1);
        }
    }


    /**
     * JDBC批量添加(循环)
     */
    @Test
    public void test() throws SQLException {

        //获取连接
        val connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);

        //插入sql语句
        String sql = "insert into students  (grade, name, gender ,score) values(?,?,?,?)";
        //创建预编译声明
        val preparedStatement = connection.prepareStatement(sql);

        long start = System.currentTimeMillis();
        //批量插入
        for (Student student : list) {
            //给查询条件赋值   索引从1开始
            preparedStatement.setObject(1, student.getGrade());
            preparedStatement.setObject(2, student.getName());
            preparedStatement.setObject(3, student.getGender());
            preparedStatement.setObject(4, student.getScore());
            //执行插入sql语句(不用传参数)
            val result = preparedStatement.executeUpdate();
            //打印返回数据（插入的记录数量）
            System.out.println(result);
        }
        long end = System.currentTimeMillis();
        System.out.println("花费时间：" + (end - start));
        preparedStatement.close();
        connection.close();
    }


    /**
     * JDBC批量添加(批处理)
     */
    @Test
    public void test1() throws SQLException {

        //获取连接
        val connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);

        //插入sql语句
        String sql = "insert into students  (grade, name, gender ,score) values(?,?,?,?)";
        //创建预编译声明
        val preparedStatement = connection.prepareStatement(sql);

        long start = System.currentTimeMillis();
        //批量插入
        for (Student student : list) {
            //给查询条件赋值   索引从1开始
            preparedStatement.setObject(1, student.getGrade());
            preparedStatement.setObject(2, student.getName());
            preparedStatement.setObject(3, student.getGender());
            preparedStatement.setObject(4, student.getScore());

            // 添加到batch
            preparedStatement.addBatch();
        }
        // 执行batch:
        int[] ns = preparedStatement.executeBatch();
        for (int n : ns) {
            // batch中每个SQL执行的结果数量
            System.out.println(n + " inserted.");
        }

        long end = System.currentTimeMillis();
        System.out.println("花费时间：" + (end - start));

        preparedStatement.close();
        connection.close();
    }

}
